﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cmsservice.Entity;
using System.Data;
using cmsservice.DAL.Hibernate;

namespace cmsservice.DAL
{
    public class Em_Grab_GaoTime_ZxDAL
    {
        public List<Em_Grab_GaoTime_Zx> Get新闻评论(string stock, int top, bool flagContent)
        {
            try
            {
                int count = top > 0 ? top : 300;
                string strCotent = flagContent ? ",content" : "";
                string sqlCmd = string.Format(@"SELECT TOP {0}
                                                        CAST(guid AS VARCHAR(36)) id ,
                                                        NewsID = Number ,
                                                        ZXType = [column] ,
                                                        title{1} ,
                                                        newssource as [source] ,
                                                        SecCode as SecuCode ,
                                                        SecAbbr as SecuAbbr ,
                                                        ReportTime
                                                FROM    EM_GRAB_GAOTIME_ZX WITH ( NOLOCK )
                                                WHERE   [column] IN ( '公司报道', '公司研究' )
                                                        AND Audit = 1
                                                        AND seccode = '{2}'
                                                        AND ReportTime < '2012-11-29'
                                                ORDER BY reporttime DESC", count, strCotent, stock);
                /*
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select top " + count + " CAST( guid as varchar(36)) id,NewsID=Number,ZXType=[column],title" + strCotent + ",newssource source,SecCode SecuCode,SecAbbr SecuAbbr,ReportTime from EM_GRAB_GAOTIME_ZX(nolock)  where [column] in  ('公司报道' ,'公司研究') ");
                sb.AppendLine("and Audit=1 and seccode=@secucode and ReportTime<'2012-11-29' order by reporttime desc");
                Tools tool = new Tools();
                tool.AddSqlParameter("secucode", System.Data.SqlDbType.VarChar,10, stock);
                 * */
                var lst = SqlHelper.GetDataByColumnsLst<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sqlCmd, null);
                return lst;
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "Get新闻评论", "softnews.asmx", ex, stock, top.ToString(), flagContent.ToString());
                throw ex;
            }
        }

        public List<Em_Grab_GaoTime_Zx> Get行业新闻(string stock, int top, bool flagContent)
        {
            try
            {
                int count = top > 0 ? top : 300;
                string strCotent = flagContent ? ",content" : "";
                string sqlCmd = string.Format(@"SELECT TOP {0}
                                                        CAST([guid] as varchar(36)) AS id ,
                                                        NewsID = Number ,
                                                        ZXType = [column] ,
                                                        title{1} ,
                                                        newssource [source] ,
                                                        SecCode SecuCode ,
                                                        SecAbbr SecuAbbr ,
                                                        ReportTime
                                                FROM    EM_GRAB_GAOTIME_ZX WITH ( NOLOCK )
                                                WHERE   [column] IN ( '行业分析' )
                                                        AND Audit = 1
                                                        AND seccode = '{2}'
                                                        AND ReportTime <= '2013-1-7'
                                                ORDER BY reporttime DESC", top, strCotent, stock);
                /* *
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select top " + count + " CAST( guid as varchar(36)) id,NewsID=Number,ZXType=[column],title" + strCotent + ",newssource source,SecCode SecuCode,SecAbbr SecuAbbr,ReportTime from EM_GRAB_GAOTIME_ZX(nolock)  where [column] in  ('行业分析') ");
                sb.AppendLine("and Audit=1 and seccode=@secucode and ReportTime<='2013-1-7' order by reporttime desc");
                Tools tool = new Tools();
                tool.AddSqlParameter("secucode", System.Data.SqlDbType.VarChar,10, stock);
                 * */
                var lst = SqlHelper.GetDataByColumnsLst<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sqlCmd, null);
                return lst;
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "Get行业新闻", "softnews.asmx", ex, stock, top.ToString(), flagContent.ToString());
                throw ex;
            }
        }

        public List<Em_Grab_GaoTime_Zx> Get行业新闻_bk(string bk, int top, bool flagContent)
        {
            try
            {
                int count = top > 0 ? top : 300;
                //string strCotent = flagContent ? ",content" : "";
                string sqlCmd = string.Format(@"SELECT TOP 300
                                                        CAST(newsid_12001 AS VARCHAR(36)) id ,
                                                        NewsID = Newsid_num ,
                                                        ZXType = '行业新闻' ,
                                                        title = f006v_12001 ,
                                                        source = f002v_12001 ,
                                                        ReportTime = f035d_12001 ,
                                                        BKCode = f005v_10202
                                                FROM    vm_dms_getnewsbycode AS A WITH ( NOLOCK )
                                                        JOIN ( SELECT   f005v_10202
                                                               FROM     tb_pub_10202 WITH ( NOLOCK )
                                                               WHERE    f002v_10202 = '二级行业'
                                                                        AND valid = '1'
                                                             ) B ON CHARINDEX(B.f005v_10202, A.f027v_12001) > 0
                                                WHERE   code IN ( '6010101', '6060201', '3070204', '3010201', '7050102',
                                                                  '3070201' )
                                                        AND f005v_10202 = '{0}'
                                                ORDER BY f035d_12001 DESC", bk);
                /*
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select TOP 300 CAST( newsid_12001 as varchar(36)) id,NewsID=Newsid_num,ZXType='行业新闻',title= f006v_12001,source=f002v_12001,ReportTime=f035d_12001,BKCode=f005v_10202");
                sb.AppendLine("	from vm_dms_getnewsbycode A");
                sb.AppendLine("		JOIN (select f005v_10202 from  tb_pub_10202 where f002v_10202 ='二级行业' and valid='1') B");
                sb.AppendLine("			ON CHARINDEX(B.f005v_10202,A.f027v_12001)>0");
                sb.AppendLine("where code in ('6010101','6060201','3070204','3010201','7050102','3070201')");
                sb.AppendLine("and  f005v_10202 = @bk");
                sb.AppendLine("order by f035d_12001 desc");
                Tools tool = new Tools();
                tool.AddSqlParameter("bk", SqlDbType.VarChar, 10, bk);
                 * */
                var lst = SqlHelper.GetDataByColumnsLst<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sqlCmd, null);
                return lst;
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "GetGet行业新闻_bk", "softnews.asmx", ex, bk, top.ToString(), flagContent.ToString());
                throw ex;
            }
        }

        public Em_Grab_GaoTime_Zx GetNewsById(string newsId)
        {
            try
            {
                string sql = "select top 10 Id=CAST(guid as varchar(36)),title,content,Source=NewsSource,SecuCode=SecCode,ReportTime,NewsID=Number,ZXType=[COLUMN],Format='TXT' from EM_GRAB_GAOTIME_ZX(nolock)  where Number=@NewsId";
                Tools tool = new Tools();
                tool.AddSqlParameter("NewsId", System.Data.SqlDbType.BigInt, 8, newsId);
                return SqlHelper.GetDataByColumns<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sql, tool.GetSqlParameters());
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "GetNewsById", "softnews.asmx", ex, newsId);
                throw ex;
            }
        }

        public Em_Grab_GaoTime_Zx GetNewsById_Guid(string guid)
        {
            try
            {
                string sql = "select top 10 Id=CAST(guid as varchar(36)),title,content,Source=NewsSource,SecuCode=SecCode,ReportTime,NewsID=Number,ZXType=[COLUMN],Format='TXT' from EM_GRAB_GAOTIME_ZX(nolock)  where guid=@guid";
                Tools tool = new Tools();
                tool.AddSqlParameter("guid", System.Data.SqlDbType.VarChar, 36, guid);
                return SqlHelper.GetDataByColumns<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sql, tool.GetSqlParameters());
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "GetNewsById_Guid", "softnews.asmx", ex, guid);
                throw ex;
            }
        }

        public List<Em_Grab_GaoTime_Zx> Get行业公司动态(List<string> stocks, int top, bool flagContent)
        {
            try
            {
                stocks = stocks.Select(x => "'" + x + "'").ToList();
                string strStock = string.Join(",", stocks);
                if (!string.IsNullOrEmpty(strStock))
                {
                    int count = top > 0 ? top : 300;
                    string strCotent = flagContent ? ",content" : "";
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("select top " + count + " CAST( guid as varchar(36)) id,NewsID=Number,ZXType=[column],title" + strCotent + ",newssource source,SecCode SecuCode,SecAbbr SecuAbbr,ReportTime=CreateTime from EM_GRAB_GAOTIME_ZX(nolock)  where [column] in  ('公司报道','公司研究') ");
                    sb.AppendLine("and Audit=1 and seccode in(" + strStock + ") order by reporttime desc");
                    var lst = SqlHelper.GetDataByColumnsLst<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sb.ToString(), null);
                    return lst;
                }
                else
                {
                    return new List<Em_Grab_GaoTime_Zx>();
                }
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "Get行业公司动态", "softnews.asmx", ex, string.Join(",",stocks),top.ToString(),flagContent.ToString());
                throw ex;
            }
        }

        public List<Em_Grab_GaoTime_Zx> Get公告_New(string stock, int top, bool flagContent, bool needPdf)
        {
            try
            {
                int count = top > 0 ? top : 300;
                string strCotent = flagContent ? ",Content=A.AnnounceContent" : "";
                string strPdf = needPdf ? "" : " and Format<>'PDF'";
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select TOP " + count + " * from (SELECT  id=cast(A.JCGUID as varchar(36)),NewsID=A.TextID,B.SecuCode,ReportTime=A.AnnounceDate,Title=A.AnnounceTitle,Format=ISNULL(C.Format,'')" + strCotent + " from ANNOUNCE_TXT_INFO(nolock) A ");
                sb.AppendLine(" JOIN ANNOUNCE_ORG_SECU(nolock) B ON A.TextID=B.TextID AND B.SecuType='A股' ");
                sb.AppendLine(" LEFT JOIN EM_STOCK_ANNOUNCE_CATEGORY(nolock) C ON A.TextID=C.TextID WHERE A.IsValid='1') as tb ");
                sb.AppendLine(" WHERE SecuCode=@stock " + strPdf);
                sb.AppendLine("ORDER BY ReportTime DESC");
                Tools tool = new Tools();
                tool.AddSqlParameter("stock", System.Data.SqlDbType.VarChar, 10, stock);
                return SqlHelper.GetDataByColumnsLst<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sb.ToString(), tool.GetSqlParameters());
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "Get公告_New", "softnews.asmx", ex, stock, top.ToString(), flagContent.ToString(), needPdf.ToString());
                throw ex;
            }
        }

        public Em_Grab_GaoTime_Zx Get公告内容_New(string id)
        {
            try
            {
                string sqlCmd = string.Format(@"SELECT  CAST( A.JCGUID as varchar(36)) AS id ,
                                                        NewsID = A.TextID ,
                                                        B.SecuCode ,
                                                        ReportTime = A.AnnounceDate ,
                                                        Title = A.AnnounceTitle ,
                                                        ISNULL(C.Format, '') AS Format ,
                                                        Content = A.AnnounceContent
                                                FROM    ANNOUNCE_TXT_INFO AS A WITH ( NOLOCK )
                                                        JOIN ANNOUNCE_ORG_SECU AS B WITH ( NOLOCK ) ON A.TextID = B.TextID AND B.SecuType = 'A股'
                                                        LEFT JOIN EM_STOCK_ANNOUNCE_CATEGORY (NOLOCK) C ON A.TextID = C.TextID
                                                WHERE   A.TextID ={0}", id);
                /*
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT id=cast( A.JCGUID as varchar(36)),NewsID= A.TextID ,B.SecuCode,ReportTime=A.AnnounceDate,Title=A.AnnounceTitle,ISNULL(C.Format,'') AS Format,Content=A.AnnounceContent from ANNOUNCE_TXT_INFO(nolock) A ");
                sb.AppendLine(" JOIN ANNOUNCE_ORG_SECU(nolock) B ON A.TextID=B.TextID AND B.SecuType='A股' ");
                sb.AppendLine(" LEFT JOIN EM_STOCK_ANNOUNCE_CATEGORY(nolock) C ON A.TextID=C.TextID ");
                sb.AppendLine("WHERE A.TextID=@id");
                Tools tool = new Tools();
                tool.AddSqlParameter("id", System.Data.SqlDbType.BigInt,8, id);
                 * */
                return SqlHelper.GetDataByColumns<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sqlCmd, null);
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "Get公告内容_New", "softnews.asmx", ex,id);
                throw ex;
            }
        }

        public List<Em_Grab_GaoTime_Zx> Get新闻评论New(string stock, int top, bool flagContent)
        {
            try
            {
                int count = top > 0 ? top : 300;
                string strCotent = flagContent ? ",F015V_12001 AS Content" : "";
                string sqlCmd = string.Format(@"SELECT TOP {0}
                                                        NewsID = newsid_num ,
                                                        CAST(newsid_12001 AS VARCHAR(36)) id ,
                                                        F035D_12001 AS ReportTime ,
                                                        F025V_12001 AS SecuCode ,
                                                        F006V_12001 AS Title{1}
                                                FROM    vm_dms_getnewsbycode WITH ( NOLOCK )
                                                WHERE   VALID = '1'
                                                        AND ischecked = '1'
                                                        AND CONVERT(VARCHAR(10), F035D_12001, 120) >= '2012-11-29'
                                                        AND F025V_12001 = '{2}'
                                                        AND code IN ( '6040201', '6040501' )
                                                ORDER BY F035D_12001 DESC", count, strCotent, stock);
                /* *
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select top " + count + " NewsID=newsid_num,cast(newsid_12001 as varchar(36) ) id,F035D_12001 ReportTime,F025V_12001 SecuCode,F006V_12001 Title" + strCotent + " from vm_dms_getnewsbycode with(nolock) where");
                sb.AppendLine("VALID='1' And  ischecked='1' and");
                sb.AppendLine("CONVERT(varchar(10),F035D_12001,120)>='2012-11-29'");
                sb.AppendLine("and F025V_12001=@stock and code in('6040201','6040501')");
                sb.AppendLine("order by F035D_12001 desc");
                Tools tool = new Tools();
                tool.AddSqlParameter("stock", SqlDbType.VarChar, 10, stock);
                 * */
                var lst = SqlHelper.GetDataByColumnsLst<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sqlCmd, null);
             
                return lst;
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "Get新闻评论New", "softnews.asmx", ex, stock,top.ToString(),flagContent.ToString());
                throw ex;
            }
        }

        public Em_Grab_GaoTime_Zx Get新闻评论New_Nr(string id)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select top 300 CAST( newsid_12001 as varchar(36)) id,F035D_12001 ReportTime,F025V_12001 SecuCode,F006V_12001 Title,F015V_12001 Content from vm_dms_getnewsbycode(nolock) where");
                long cc = 0;
                Tools tool = new Tools();
                if (long.TryParse(id, out cc))
                {
                    sb.AppendLine("newsid_num=@id");
                }
                else
                {
                    sb.AppendLine("newsid_12001=@id");
                }
             
                tool.AddSqlParameter("id", SqlDbType.VarChar, 36, id);
                return SqlHelper.GetDataByColumns<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sb.ToString(), tool.GetSqlParameters());
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "Get新闻评论New_Nr", "softnews.asmx", ex, id);
                throw ex;
            }
        }

        public Em_Grab_GaoTime_Zx Get新闻评论New2_Nr(string id)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select  CAST( newsid_12001 as varchar(36)) id,NewsID=Newsid_num,ZXType='行业新闻',title= f006v_12001,source=f002v_12001,ReportTime=f035d_12001,BKCode=f027v_12001,Content=f015v_12001   from vm_dms_getnewsbycode(nolock)");
                sb.AppendLine("where code in ('6010101','6060201','3070204','3010201','7050102','3070201')");
                sb.AppendLine("and Newsid_num=@newsid");
                Tools tool = new Tools();
                tool.AddSqlParameter("newsid", SqlDbType.VarChar, 36, id);
                var c = SqlHelper.GetDataByColumns<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sb.ToString(), tool.GetSqlParameters());
                if (c == null)
                {
                    return new Em_Grab_GaoTime_Zx();
                }
                else
                {
                    return c;
                }
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "Get新闻评论New2_Nr", "softnews.asmx", ex, id);
                throw ex;
            }
        }

        public Em_Grab_GaoTime_Zx Get新闻评论New2_Nr_Guid(string guid)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("select  CAST( newsid_12001 as varchar(36)) id,NewsID=Newsid_num,ZXType='行业新闻',title= f006v_12001,source=f002v_12001,ReportTime=f035d_12001,BKCode=f027v_12001,Content=f015v_12001   from vm_dms_getnewsbycode(nolock)");
                sb.AppendLine("where code in ('6010101','6060201','3070204','3010201','7050102','3070201')");
                sb.AppendLine("and newsid_12001=@newsid");
                Tools tool = new Tools();
                tool.AddSqlParameter("newsid", SqlDbType.VarChar, 36, guid);
                var c = SqlHelper.GetDataByColumns<Em_Grab_GaoTime_Zx>(DBConn.NewDMSConnstr, sb.ToString(), tool.GetSqlParameters());
                if (c == null)
                {
                    return new Em_Grab_GaoTime_Zx();
                }
                else
                {
                    return c;
                }
            }
            catch (Exception ex)
            {
                CLogDAL clogDAL = new CLogDAL();
                clogDAL.Add("Em_Grab_GaoTime_ZxDAL", "Get新闻评论New2_Nr_Guid", "softnews.asmx", ex, guid);
                throw ex;
            }
        }

      
    }
}
